import pymysql
from db import config
class Model:
	'''单表信息操作类'''
	tab_name=None #表名
	link=None #数据库连接对象
	cursor=None #游标对象
	pk='id' #表的主键名字
	fields=[]


	def __init__(self,table,config=config):

		try:
			self.tab_name=table;
			self.link=pymysql.connect(host=config.HOST,user=config.USER,password=config.PASSWD,db=config.DBNAME,charset="utf8")
			self.cursor=self.link.cursor(pymysql.cursors.DictCursor)#把他变成列表里面放字典的形式
			self.__loadFields()
		except Exception as err:
			print("SQL连接初始化错误：%s"%err)

	def __loadFields(self):
		sql="desc %s"%(self.tab_name)
		self.cursor.execute(sql)
		dlist=self.cursor.fetchall()
		#赋值字段信息
		for i in dlist:
		 	self.fields.append(i['Field'])
		 	#赋值主键
		 	if i['Key']=='PRI':
		 		self.pk=i.get('Field')
		

	def __del__(self):
		if self.link!=None:
			self.link.close()
		if self.cursor!=None:
			self.cursor.close()

	def findAll(self):
		'''获取当前表的所有信息，没有信息返回空列表'''
		try:
			sql='select * from %s'%(self.tab_name)
			# print(sql)
			self.cursor.execute(sql)
			return self.cursor.fetchall()
		except Exception as err:
			print("查询错误，原因",err)
			return []

	def findOne(self,id):
		'''获取指定id的单条信息，没有的话返回null'''
		try:
			sql='select * from %s where %s="%d"'%(self.tab_name,self.pk,id)
			self.cursor.execute(sql)
			# print(sql)
			return self.cursor.fetchone()#如果找不到默认返回的是none
		except Exception as e:
			print("查询错误，原因",err)
			return None1
	
	def select(self,where=None,order=None,limit=None):
		'''带条件的信息查询'''
		try:
			sql='select * from %s '%(self.tab_name)
			if isinstance(where,list) and len(where)>0:
				sql+=" where "+" and ".join(where)

			if order is not None:
				sql+=" order by "+order
			if limit is not None:
				sql+=" limit "+str(limit)
			self.cursor.execute(sql)
			# print(sql)
			return self.cursor.fetchall()#如果找不到默认返回的是none
		except Exception as e:
			print("查询错误，原因",e)
			return None

	def save(self,data={}):
		try:
			keys=[];
			values=[]
			for k,v in data.items():
				if k in self.fields:
					keys.append(k)
					values.append(str(v))
			sql='insert into %s(%s) values(%s)'%(self.tab_name,",".join(keys),",".join(["'%s'"]*len(values)))
			
			sql=sql%(tuple(values))
			# print(sql)
			self.cursor.execute(sql)
			self.link.commit()
			#返回自增id号
			return self.cursor.lastrowid
		except Exception as e:
			self.link.rollback()
			print("数据保存错误，原因",e)
			return None
	def update(self,data={}):
		'''修改一条数据'''
		try:
			values=[]
			for k,v in data.items():
				if (k in self.fields) and k!=self.pk:
					values.append("%s='%s'"%(k,v))
			sql='update %s set %s where %s=%s'%(self.tab_name,",".join(values),self.pk,data[self.pk])
			
			# print(sql)
			self.cursor.execute(sql)
			self.link.commit()
			#返回自增id号
			return self.cursor.rowcount
		except Exception as e:
			self.link.rollback()
			print("数据修改错误，原因",e)
			return None
	def delete(self,id):
		'''删除一条信息'''
		try:
			sql="delete from %s where %s=%d"%(self.tab_name,self.pk,id)
			self.cursor.execute(sql)
			self.link.commit()
			#返回影响行数
			return self.cursor.rowcount;
		except Exception as e:
			print("数据删除错误，原因",e)
			return None